﻿CREATE PROCEDURE [dbo].[proc_Order_Getlist_NOE_Baidu_In]
	(
		@InCompanyId varchar(500),
		@StateId int,
		@TypeId int,
		--@WayId int,
		--@ProcessId int,
		@Stext nvarchar(50),
		@StartDate nvarchar(10),
		@EndDate nvarchar(10),
		@StartIndex int,
		@EndIndex int,
		@NOE_Flag int,
		@DateType int
	)
As
Begin

Declare @cSql varchar(2000),@cRq varchar(20)

Set @cSql = 'CompanyId In('+@InCompanyId+')'

If @StateId<>0
	Set @cSql = @cSql + ' And StateId='+Convert(varchar(10),@StateId)

If @TypeId<>0
	Set @cSql = @cSql + ' And TypeId='+Convert(varchar(10),@TypeId)

--If @WayId<>0
--	Set @cSql = @cSql + ' And WayId='+Convert(varchar(10),@WayId)

--If @ProcessId<>0
--	Set @cSql = @cSql + ' And ProcessId='+Convert(varchar(10),@ProcessId)

If @Stext<>''
	Set @cSql = @cSql + ' And (CusName like ''%'+@Stext+'%'' Or Salesman like ''%'+@Stext+'%'')'

If @NOE_Flag<>100
	Set @cSql = @cSql + ' And NOE_Flag=' + Convert(varchar(10),@NOE_Flag)
Else
	Set @cSql = @cSql + ' And NOE_Flag<>1'

If @DateType=1
	Set @cRq = 'FinishDate'
Else
	Set @cRq = 'CreateDate'

Set @cSql = @cSql + ' And '+@cRq+'>=@sRq And '+@cRq+'<=@eRq'

Set @cSql='Declare @sRq Datetime,@eRq Datetime
Set @sRq = Convert(Datetime,''' + @StartDate + ' 00:00:00'')
Set @eRq = Convert(Datetime,''' + @EndDate + ' 23:59:59'')
;WITH list As(Select ROW_NUMBER() OVER (Order By '+@cRq+' Desc,id Desc)AS Row
		,Id
		,OId
		,OrderId
		,CusId
		,CusName
		,CusPerson
		,CusPersonTel
		,CusDescription
		,CusEstimate
		,TypeId
		,TypeName
		,WayId
		,WayName
		,ProcessId
		,ProcessName
		,Salesman
		,SalesmanAccount
		,IsNull(FinishDate,''1900-01-01'') As TradeDate
		,CreateDate
		,YingShouPrice
		,IsNull(ShiShouPrice,0) As ShiShouPrice
		,Discount
		,PaymentTypeId
		,PaymentType
		,BankId
		,Bank
		,PaymentDescription
		,OtherDescription
		,DepId
		,CompanyId
		,StateId
,IsGreen
		,IsNull((Select Title From Order_State Where Id=[Order].StateId),''...'') As StateName
,NOE_Flag
		,ISNULL((SELECT SUM(Project.SalePrice-Project.CostPrice) FROM Project WHERE Project.OrderId=[order].OId),0)AS MaoLi
		From [Order]
		Where '+@cSql + '
	)
	
	Select *,(Select Count(0) From list) As RecordCount 
			,(SELECT SUM(ShiShouPrice) FROM list) AS ShiShouSUM
			,(SELECT SUM(YingShouPrice) FROM list) AS YingShouSUM
			,(SELECT SUM (MaoLi) FROM list )AS MaoLiSUM
	From list Where Row Between '+Convert(varchar(10),@StartIndex)+' and '+Convert(varchar(10),@EndIndex)+' Order By Row'

Exec(@cSql)
--Select  @cSql
End
